{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "440bde29-6c37-4c86-a5f5-12ab7cdaa844",
   "metadata": {},
   "source": [
    "## A04 Validate the Data\n",
    "\n",
    "This code is used for data quality verification and basic data analysis"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "04e84187-17d5-41f6-829e-4c2ac92c2918",
   "metadata": {},
   "source": [
    "### 1. init spark session"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "4b236933-c7c9-447a-a5cc-1743759f414e",
   "metadata": {},
   "outputs": [],
   "source": [
    "import findspark\n",
    "findspark.init()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "659686fe-8a37-462c-9b6a-b966f63fcf0f",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql import SparkSession\n",
    "spark = SparkSession.builder.appName(\"price_opendata\").getOrCreate()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ba128c91-00a7-409c-969d-22a43b0b79eb",
   "metadata": {},
   "source": [
    "### 2. read formatted data and exploitation data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "00c5709d-d95d-4291-8b8e-c50a84212713",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_formatted = spark.read.parquet(\"./Formatted Zone/price_opendata.parquet\")\n",
    "df = spark.read.parquet(\"./Exploitation Zone/price_opendata.parquet\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "548db6da-4b8f-41ad-9148-7c3585fc78a8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "359"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_formatted.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "2dc55002-a7fa-4a62-95f6-56c6e2fbec30",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "359"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "17449b15-10cf-4af6-984f-fded36418072",
   "metadata": {},
   "source": [
    "The number of rows is the same before and after data cleaning"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "35d39145-d025-4ad6-bfa9-c5735d808047",
   "metadata": {},
   "source": [
    "### 3. Missing Values Statistics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "fc1d2e80-5925-47ee-bd19-bff50d0b61d0",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql.functions import when, col, sum"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "145ae445-f2cd-4080-ac77-40284f67e50c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+-----------+-------------+----------+------+--------+----------+------------+----------+------------+----+\n",
      "|_id|district_id|district_name|neigh_name|Amount|PerMeter|diffAmount|diffPerMeter|usedAmount|usedPerMeter|year|\n",
      "+---+-----------+-------------+----------+------+--------+----------+------------+----------+------------+----+\n",
      "|  0|          0|            0|         0|     0|       0|         0|           0|         0|           0|   0|\n",
      "+---+-----------+-------------+----------+------+--------+----------+------------+----------+------------+----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "missing_values_df = df.select(\n",
    "    [sum(when(col(column).isNull(), 1).otherwise(0)).alias(column) for column in df.columns]\n",
    ")\n",
    "\n",
    "missing_values_df.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a6d89e1b-d153-4b74-af83-e6371dfbd8e4",
   "metadata": {},
   "source": [
    "### 4. Statistics of the number of data entries in different columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "2a7cc229-14e5-4114-9742-eab1d43faf48",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- _id: long (nullable = true)\n",
      " |-- district_id: long (nullable = true)\n",
      " |-- district_name: string (nullable = true)\n",
      " |-- neigh_name: string (nullable = true)\n",
      " |-- Amount: double (nullable = true)\n",
      " |-- PerMeter: double (nullable = true)\n",
      " |-- diffAmount: double (nullable = true)\n",
      " |-- diffPerMeter: double (nullable = true)\n",
      " |-- usedAmount: double (nullable = true)\n",
      " |-- usedPerMeter: double (nullable = true)\n",
      " |-- year: long (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "f6174031-0ab1-418e-adda-ce55377c7f67",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+-----+\n",
      "|year|count|\n",
      "+----+-----+\n",
      "|2014|   71|\n",
      "|2016|   73|\n",
      "|2017|   72|\n",
      "|2013|   72|\n",
      "|2015|   71|\n",
      "+----+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.groupby(\"year\").count().show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "d862408a-2910-417c-9794-0ccae5afd710",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+-------------------+-----+\n",
      "|district_id|      district_name|count|\n",
      "+-----------+-------------------+-----+\n",
      "|          2|           Eixample|   30|\n",
      "|          9|        Sant Andreu|   34|\n",
      "|          8|         Nou Barris|   63|\n",
      "|          5|Sarrià-Sant Gervasi|   30|\n",
      "|          6|             Gràcia|   25|\n",
      "|          3|     Sants-Montjuïc|   38|\n",
      "|          4|          Les Corts|   15|\n",
      "|          7|     Horta-Guinardó|   54|\n",
      "|         10|         Sant Martí|   50|\n",
      "|          1|       Ciutat Vella|   20|\n",
      "+-----------+-------------------+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.groupby(\"district_id\", \"district_name\").count().show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "40946ccb-dbcf-4b2f-b34c-b2854aafaea6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+-----+\n",
      "|          neigh_name|count|\n",
      "+--------------------+-----+\n",
      "|         el Poblenou|    5|\n",
      "|   la Vila de Gràcia|    5|\n",
      "|el Besòs i el Mar...|    5|\n",
      "|        la Guineueta|    5|\n",
      "|        la Teixonera|    5|\n",
      "|la Dreta de l'Eix...|    5|\n",
      "|         el Guinardó|    5|\n",
      "|      el Barri Gòtic|    5|\n",
      "|            Vallbona|    5|\n",
      "|           Canyelles|    5|\n",
      "|Provençals del Po...|    5|\n",
      "| la Verneda i la Pau|    5|\n",
      "|Vilapicina i la T...|    5|\n",
      "|               Navas|    5|\n",
      "|l'Antiga Esquerra...|    5|\n",
      "|   la Marina de Port|    5|\n",
      "|              Sarrià|    5|\n",
      "|la Marina del Pra...|    3|\n",
      "|          Torre Baró|    4|\n",
      "|    la Trinitat Nova|    5|\n",
      "+--------------------+-----+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.groupby(\"neigh_name\").count().show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "a41153b4-7beb-460d-a24b-d2ccbd94149d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- _id: long (nullable = true)\n",
      " |-- district_id: long (nullable = true)\n",
      " |-- district_name: string (nullable = true)\n",
      " |-- neigh_name: string (nullable = true)\n",
      " |-- Amount: double (nullable = true)\n",
      " |-- PerMeter: double (nullable = true)\n",
      " |-- diffAmount: double (nullable = true)\n",
      " |-- diffPerMeter: double (nullable = true)\n",
      " |-- usedAmount: double (nullable = true)\n",
      " |-- usedPerMeter: double (nullable = true)\n",
      " |-- year: long (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.printSchema()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "76302bb1-58de-4e50-8cd4-4abebf20db70",
   "metadata": {},
   "source": [
    "### 5. describe the amount he PerMeter fields"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "697c2b6c-9496-4c21-bb04-6728492c1198",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+------------------+------------------+------------------+------------------+------------------+-----------------+\n",
      "|summary|            Amount|          PerMeter|        diffAmount|      diffPerMeter|        usedAmount|     usedPerMeter|\n",
      "+-------+------------------+------------------+------------------+------------------+------------------+-----------------+\n",
      "|  count|               359|               359|               359|               359|               359|              359|\n",
      "|   mean|239.66350974930364| 2732.004735376044| 296.4895238095231|3277.7412322274986|237.67450980392164|2708.732212885151|\n",
      "| stddev|150.50403725145722|1073.8223673650552|152.82810200495456| 992.3805903025107|151.49543642332188| 1073.36286501758|\n",
      "|    min|              48.5|             342.6|              73.0|             342.6|              48.5|            612.6|\n",
      "|    max|             890.0|            6951.3|            1642.0|            7786.2|             890.0|           7020.2|\n",
      "+-------+------------------+------------------+------------------+------------------+------------------+-----------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.select(\"Amount\", \"PerMeter\", \"diffAmount\", \"diffPerMeter\", \"usedAmount\", \"usedPerMeter\").describe().show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "57b5c4f1-d6ff-47f1-805f-10f1b6ca5cb9",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.19"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
